#include "query.h"
#include <QVariant>
#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <iostream>
#include <QtDebug>
#include "ktv/gui/list/mylist.h"
#include "ktv/gui/listitem/song.h"
#include "ktv/gui/listitem/singer.h"

QList<MyItem *> * Query::queryCHSingers()
{
       QSqlQuery query;
       QString qs = "select * from singer where area = 'nd' order by spell";
       query.exec(qs);
       QList<MyItem *> * singerList = new QList<MyItem *>;
       while(query.next())
       {
         Singer * singer=new Singer;
         singer->setData(MyList::NAME,query.value(1).toString());
         singer->setPixmapLocation(query.value(5).toString());
         singerList->append(singer);
       }
       return  singerList;
}
QList<MyItem *> * Query::queryHKSingers()
 {

    QSqlQuery query;
    QString qs = "select * from singer where area in ('tw','xg') order by spell";
    query.exec(qs);
    QList<MyItem *> * singerList = new QList<MyItem *>;
    while(query.next())
    {
      Singer * singer=new Singer;
      singer->setData(MyList::NAME,query.value(1).toString());
      singer->setPixmapLocation(query.value(5).toString());
      singerList->append(singer);
    }
    return  singerList;
 }

QList<MyItem *> * Query::queryAUSingers()
  {

    QSqlQuery query;
    QString qs = "select * from singer where area = 'om' order by spell";
    query.exec(qs);
    QList<MyItem *> * singerList  = new QList<MyItem *>;
    while(query.next())
    {
      Singer * singer=new Singer;
      singer->setData(MyList::NAME,query.value(1).toString());
      singer->setPixmapLocation(query.value(5).toString());
      singerList->append(singer);
    }
    return  singerList;
  }

bool Query::createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("virsulaktv");
    db.setUserName("root");
    db.setPassword("root");
    if (!db.open()) {
        std::cout<<"don't open"<<std::endl;
        return false;
    }
    std::cout<<"open"<<std::endl;
    return true;
}

QList<MyItem *> * Query::querySingerSongs(QString info)
  {
        QSqlQuery query;
        QString str1 = "select * from singer where infophoto = ";
        QString str2="'";
        QString qs=str1.append(str2).append(info).append(str2);
        query.exec(qs);
        QString  singerId;
        while(query.next())
       {
           singerId=query.value(0).toString();
       }
        QString str11 = "select * from song where singerid = ";
        QString str21 = "'";
        QString qs1 = str11.append(str21).append(singerId).append(str21);
        query.exec(qs1);
        QList<MyItem *> * songList = new QList<MyItem *>;
         while(query.next())
        {
          Song * song=new Song;
          song->setData(MyList::NAME,query.value(2).toString());
          song->setMVLocation(query.value(4).toString());
          song->setPixmapLocation(query.value(6).toString());
          songList->append(song);
        }
        return songList;
  }

QList<MyItem *> * Query::querySongs(QString spell)
{
   QSqlQuery query;
   QString str1="select * from song where spell like ";
   QString str2="'";
   QString str3="%";
   QString order=" order by spell";
   QString qs= str1.append(str2).append(spell).append(str3).append(str2).append(order);
   query.exec(qs);
   qDebug() << "after exec";
   QList<MyItem *> * songList = new QList<MyItem *>;
    while(query.next())
   {
        qDebug() << "bian li";
     Song * song=new Song;
     song->setData(MyList::NAME,query.value(2).toString());
     song->setMVLocation(query.value(4).toString());
     song->setPixmapLocation(query.value(6).toString());
     songList->append(song);
   }
    qDebug() << "over";
   return songList;
}
QList<MyItem *> * Query::querySingers(QString spell)
{
   QSqlQuery query;
   QString str1="select * from singer where spell like ";
   QString str2="'";
   QString str3="%";
   QString order=" order by spell";
   QString qs= str1.append(str2).append(spell).append(str3).append(str2).append(order);
   query.exec(qs);
   QList<MyItem *> *singerList = new QList<MyItem *>;
   while(query.next())
  {
    Singer * singer=new Singer;
    singer->setData(MyList::NAME,query.value(1).toString());
    singer->setPixmapLocation(query.value(5).toString());
    singerList->append(singer);
  }
   return singerList;
}
QList<MyItem *> * Query::queryAllSongs()
{
    QSqlQuery query;
    QString qs="select * from song order by spell";
    query.exec(qs);
    QList<MyItem *> *songList = new QList<MyItem *>;
    while(query.next())
    {
      Song * song=new Song;
      song->setData(MyList::NAME,query.value(2).toString());
      song->setMVLocation(query.value(4).toString());
      song->setPixmapLocation(query.value(6).toString());
      songList->append(song);
    }
    return songList;
}
QList<MyItem *> * Query::queryAllSingers()
{
    QSqlQuery query;
    QString qs="select * from singer order by spell";
    query.exec(qs);
    QList<MyItem *> *singerList = new QList<MyItem *>;
    while(query.next())
    {
      Singer * singer=new Singer;
      singer->setData(MyList::NAME,query.value(1).toString());
      singer->setPixmapLocation(query.value(5).toString());
      singerList->append(singer);
    }
    return  singerList;
}
MyItem * Query::queryASong(QString location)
{
    QSqlQuery query;
    QString q1="select * from song where location = ";
    QString qs=q1.append("'").append(location).append("'");
    query.exec(qs);
    Song *song;
    while(query.next()) {
        song=new Song;
        song->setData(MyList::NAME,query.value(2).toString());
        song->setMVLocation(query.value(4).toString());
        song->setPixmapLocation(query.value(6).toString());
    }
    return song;
}

Query::Query()
{
  createConnection();
}
